﻿/****************************** Module Header ******************************\
 * Module Name:  mxDataTable.cs
 * Module Version:  1.0.0 (2012-4-27)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to extension collection instance's method.  It supplies 
 * useful extension methods, such as ToString(), FetchString() and so on.
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace UCL
{

    /// <summary>
    /// 数据表对象扩展类
    /// </summary>
    public static class mxDataTable
    {
        #region 增删改查

        /// <summary>
        /// 联合多个表
        /// <para>2012-10-30</para>
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="dts"></param>
        /// <returns></returns>
        public static DataTable Join(this DataTable dt, params DataTable[] dts)
        {
            foreach (DataTable o in dts)
            {
                foreach (DataRow dr in o.Rows)
                {
                    dt.Rows.Add(dr.ItemArray);
                }
            }
            return dt;
        }

        /// <summary>
        /// 检测指定列是否存在与指定的值相等的记录
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="v">要比较的值</param>
        /// <param name="colIndex">要比较的列索引编号</param>
        /// <returns></returns>
        public static bool Exists(this DataTable dt, object v, int colIndex = 0)
        {
            if (dt == null || dt.Rows.Count == 0) return false;
            foreach (DataRow r in dt.Rows)
            {
                if (r[colIndex].ToString() == v.ToString()) return true;
            }
            return false;
        }

        /// <summary>
        /// 移除指定列中与指定值相等的数据行
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="v">要比较的值</param>
        /// <param name="colIndex">值对应的列索引编号</param>
        public static void RemoveByValue(this DataTable dt, object v, int colIndex = 0)
        {
            if (dt == null || dt.Rows.Count == 0) return;
            int i = -1;
            foreach (DataRow r in dt.Rows)
            {
                if (r[colIndex].ToString() == v.ToString()) i = dt.Rows.IndexOf(r);
            }
            if (i > -1) dt.Rows.RemoveAt(i);
        }

        /// <summary>
        /// 移除指定数据行索引编号所在的数据行
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="rowIndex"></param>
        public static void RemoveByRowIndex(this DataTable dt, int rowIndex)
        {
            dt.Rows.RemoveAt(rowIndex);
        }

        /// <summary>
        /// 移除空行
        /// <para>2013-8-21 by sutroon</para>
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="colKeyIndex"></param>
        /// <returns></returns>
        public static DataTable RemoveEmptyRow(this DataTable dt, int colKeyIndex)
        {
            object v;
            for (int i = dt.Rows.Count - 1; i > -1; i--)
            {
                v = dt.Rows[i][colKeyIndex];
                if (v == DBNull.Value || v.ToString().Trim().Length == 0) dt.Rows.RemoveAt(i);
            }
            return dt;
        }

        /// <summary>
        /// 添加一条数据行,并检测是否主键值重复
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="pkColIndex">主键值所在的数据列的索引编号</param>
        /// <param name="vals"></param>
        public static void AddRow(this DataTable dt, int pkColIndex = 0, params object[] vals)
        {
            if (dt == null || dt.Columns.Count == 0 || vals.Length == 0) return;
            foreach (DataRow r in dt.Rows)
            {
                if (r[pkColIndex].ToString() == vals[pkColIndex].ToString()) return;
            }
            DataRow dr = dt.NewRow();
            dr.ItemArray = vals;
            dt.Rows.InsertAt(dr, 0);
        }

        /// <summary>
        /// 添加行并返回新数据表
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static DataTable AppendRowR(this DataTable dt, params object[] args)
        {
            dt.Rows.Add(args);
            return dt;
        }

        /// <summary>
        /// 添加行并返回新数据表
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        /// 
        public static DataTable PrependRowR(this DataTable dt, params object[] args)
        {
            DataRow dr = dt.NewRow();
            dr.ItemArray = args;
            dt.Rows.InsertAt(dr, 0);
            return dt;
        }
        /// <summary>
        /// 更新指定的数据行索引编号对应的数据行数据
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="rowId">数据行索引编号</param>
        /// <param name="colsIndex">要更新的数据列索引编号集合</param>
        /// <param name="vals">要更新的数据值集合</param>
        public static void UpdateByRowIndex(this DataTable dt, int rowId, int[] colsIndex, params object[] vals)
        {
            if (dt == null || dt.Rows.Count == 0) return;
            for (int i = 0; i < colsIndex.Length; i++)
            {
                dt.Rows[rowId][colsIndex[i]] = vals[i];
            }
        }

        /// <summary>
        /// 更新符合条件的数据行并返回新的数据表
        /// <para>如： dgvList.GetSearchableColumns().Update("Name='出库时间'", new string[] { "ID" }, "CONVERT(VARCHAR(10),CreatedTime,120)");</para>
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filter"></param>
        /// <param name="fields"></param>
        /// <param name="vals"></param>
        /// <returns></returns>
        public static DataTable Update(this DataTable dt, string filter, string[] fields, params string[] vals)
        {
            DataRow[] rs = dt.Select(filter);
            if (rs.Length == 0) return dt;
            int i;
            foreach (DataRow r in rs)
            {
                i = 0;
                foreach (string s in fields)
                {
                    r[s] = vals[i];
                    i++;
                }
            }
            return dt;
        }

        /// <summary>
        /// 合并多个表的数据
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="dts"></param>
        /// <returns></returns>
        /// <remarks>1.0.0 2013-10-17 sutroon</remarks>
        public static DataTable MergeTables(this DataTable dt, params DataTable[] dts)
        {
            foreach (DataTable dtTemp in dts)
            {
                foreach (DataRow r in dtTemp.Rows)
                {
                    dt.Rows.Add(r.ItemArray);
                }
            }
            return dt;
        }

        /// <summary>
        /// 获取单元格的值
        /// <para>如：dt.GetValue&lt;short&gt;(r => { return r["Sex"] });</para>
        /// <para>取代GetValue,GetString,GetInt,GetDouble,GetStringByFilter,GetDateTime,GetGuid方法</para>
        /// <para>不用担心DBNull值，在方法中已经加入判断!</para>
        /// </summary>
        /// <typeparam name="V"></typeparam>
        /// <param name="dt"></param>
        /// <param name="func"></param>
        /// <param name="defv"></param>
        /// <param name="rowIndex"></param>
        /// <param name="emptyAsNull">是否把空字符串当成null判断</param>
        /// <returns></returns>
        /// <remarks>
        /// 2013-7-2 1.0 by sutroon
        /// 2014-2-17 2.0 by sutroon
        /// </remarks>
        public static V GetValue<V>(this DataTable dt, Func<DataRow, object> func, V defv = default(V), int rowIndex = 0, bool emptyAsNull = false, string filter = "")
        {
            if (dt == null || dt.Rows.Count == 0) return defv;
            DataRow r;
            if (filter.Length > 0)
            {
                r = dt.Select(filter).FirstOrDefault();
            }
            else
            {
                r = dt.Rows[rowIndex];
            }
            object v = func(r);
            if (v == DBNull.Value || (emptyAsNull && string.IsNullOrEmpty(v.ToString()))) return defv;
            if (typeof(V) == typeof(string) && v.GetType() != typeof(string)) return (V)((object)v.ToString());
            return (V)v;
        }
        /// <summary>
        /// 获取单元格的值
        /// <para>如：dt.GetValue&lt;short&gt;("Sex");</para>
        /// </summary>
        /// <typeparam name="V"></typeparam>
        /// <param name="dt"></param>
        /// <param name="colName"></param>
        /// <param name="defv"></param>
        /// <param name="rowIndex"></param>
        /// <param name="emptyAsNull"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        /// <remarks>2014-2-24 by sutroon</remarks>
        public static V GetValue<V>(this DataTable dt, string colName, V defv = default(V), int rowIndex = 0, bool emptyAsNull = false, string filter = "")
        {
            if (!dt.Columns.Contains(colName)) return defv;
            return dt.GetValue<V>(r => { return r[colName]; }, defv, rowIndex, emptyAsNull, filter);
        }
        #endregion

        #region 特殊查询

        /// <summary>
        /// 统计指定列的数值
        /// <para>dt.Sum&lt;double&gt;(r =&gt; { return r["SubTotal"];});</para>
        /// <para>取代SumDouble,SumInteger等方法</para>
        /// </summary>
        /// <typeparam name="V"></typeparam>
        /// <param name="dt"></param>
        /// <param name="act"></param>
        /// <returns></returns>
        /// <remarks>2014-2-17 2.0 by sutroon</remarks>
        public static V Sum<V>(this DataTable dt, Func<DataRow, object> func)
        {
            if (dt == null || dt.Rows.Count == 0) return default(V);
            V sumv = default(V);
            object v;
            int iv = 0; double dblv = 0; float fltv = 0; decimal decv = 0; byte bv = 0; sbyte sbv = 0; uint uv = 0; long lv = 0; ulong ulv = 0; short sv = 0; ushort usv = 0;
            foreach (DataRow r in dt.Rows)
            {
                v = func(r);
                if (v == DBNull.Value) continue;
                switch (sumv.GetType().Name)
                {
                    case "Int32":
                        iv += (Int32)v;
                        sumv = (V)(Object)iv;
                        break;
                    case "Double":
                        dblv += (double)v;
                        sumv = (V)(Object)dblv;
                        break;
                    case "Single":
                        fltv += (float)v;
                        sumv = (V)(Object)fltv;
                        break;
                    case "Decimal":
                        decv += (decimal)v;
                        sumv = (V)(Object)decv;
                        break;
                    case "Int16":
                        sv += (short)v;
                        sumv = (V)(Object)sv;
                        break;
                    case "Int64":
                        lv += (long)v;
                        sumv = (V)(Object)lv;
                        break;
                    case "UInt16":
                        usv += (ushort)v;
                        sumv = (V)(Object)usv;
                        break;
                    case "UInt32":
                        uv += (uint)v;
                        sumv = (V)(Object)uv;
                        break;
                    case "UInt64":
                        ulv += (ulong)v;
                        sumv = (V)(Object)ulv;
                        break;
                    case "Byte":
                        bv += (byte)v;
                        sumv = (V)(Object)bv;
                        break;
                    case "SByte":
                        sbv += (sbyte)v;
                        sumv = (V)(Object)sbv;
                        break;
                }
            }
            return sumv;
        }

        /// <summary>
        /// 获取符合条件的数据列的值列表
        /// </summary>
        /// <typeparam name="V"></typeparam>
        /// <param name="dt"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        /// <example>
        /// <![CDATA[
        /// dt.GetListOfColumnValue<V>(r => { return r.Field<V>(colName); });
        /// ]]>
        /// </example>
        /// <remarks>2014-2-17 2.0 by sutroon</remarks>
        public static IList<V> GetListOfColumnValue<V>(this DataTable dt, Func<DataRow, V> func)
        {
            IList<V> list = new List<V>();
            foreach (DataRow r in dt.Rows)
            {
                list.Add(func(r));
            }
            return list;
        }

        /// <summary>
        /// 获取符合条件的数据列的值列表
        /// </summary>
        /// <typeparam name="V"></typeparam>
        /// <param name="dt"></param>
        /// <param name="colName"></param>
        /// <returns></returns>
        /// <remarks>2014-4-29 by sutroon</remarks>
        public static IList<V> GetListOfColumnValue<V>(this DataTable dt, string colName)
        {
            return dt.GetListOfColumnValue<V>(r => { return r.Field<V>(colName); });
        }

        /// <summary>
        /// 获取符合条件的数据列的值列表
        /// </summary>
        /// <typeparam name="V"></typeparam>
        /// <param name="dt"></param>
        /// <param name="colIndex"></param>
        /// <returns></returns>
        /// <remarks>2014-4-29 by sutroon</remarks>
        public static IList<V> GetListOfColumnValue<V>(this DataTable dt, int colIndex)
        {
            return dt.GetListOfColumnValue<V>(r => { return r.Field<V>(colIndex); });
        }

        /// <summary>
        /// 获取数据表中的数据列的属性列表
        /// <para>如：dt.GetListOfColumnProperties(c =&gt; c.ColumnName);</para>
        /// <para>替代GetListOfFieldName方法</para>
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        /// <remarks>2014-2-17 2.0 by sutroon</remarks>
        public static IList<string> GetListOfColumnProperties(this DataTable dt, Func<DataColumn, string> func)
        {
            IList<string> list = new List<string>();
            foreach (DataColumn c in dt.Columns)
            {
                list.Add(func(c));
            }
            return list;
        }

        #endregion

        #region 架构修改

        /// <summary>
        /// 设置表格列名
        /// <para>如果是新表格则创建列，如果已有列则更改列名</para>
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="names"></param>
        public static void SetColumnsName(this DataTable dt, params string[] names)
        {
            if (dt.Columns.Count == 0)
            {
                foreach (string s in names)
                {
                    dt.Columns.Add(s);
                }
                return;
            }
            int i = 0;
            foreach (string s in names)
            {
                dt.Columns[i].ColumnName = s;
                i++;
            }
        }

        /// <summary>
        /// 追加表格列名称
        /// <para>如果列名已存在则跳过</para>
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="names"></param>
        public static void AppendColumnsName(this DataTable dt, params string[] names)
        {
            foreach (string s in names)
            {
                if (!dt.Columns.Contains(s)) dt.Columns.Add(s);
            }
        }

        /// <summary>
        /// 设置表格名称
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public static DataTable SetTableName(this DataTable dt, string name)
        {
            dt.TableName = name;
            return dt;
        }

        /// <summary>
        /// 复制表结构
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static DataTable CopySehcma(this DataTable dt)
        {
            DataTable dtn = new DataTable();
            foreach (DataColumn dc in dt.Columns)
            {
                dtn.Columns.Add(dc.ColumnName, dc.DataType);
            }
            if (dt.TableName.Length > 0) dtn.TableName = dt.TableName;
            return dtn;
        }

        /// <summary>
        /// 将指定的行移到最顶部
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        public static DataTable MoveTop(this DataTable dt, string filter)
        {
            DataRow r = dt.Select(filter).FirstOrDefault();
            if (r == null || dt.Rows.IndexOf(r) == 0) return dt;
            DataRow r1 = dt.NewRow();
            r1.ItemArray = r.ItemArray;
            dt.Rows.Remove(r);
            dt.Rows.InsertAt(r1, 0);
            return dt;
        }

        /// <summary>
        /// 将指定的行上移一行
        /// <para>如果移动失败则i=-1</para>
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filter"></param>
        /// <param name="i"></param>
        /// <returns></returns>
        public static DataTable MoveUp(this DataTable dt, string filter, out int i)
        {
            DataRow r = dt.Select(filter).FirstOrDefault();
            if (r == null)
            {
                i = -1;
                return dt;
            }
            i = dt.Rows.IndexOf(r);
            if (i == 0)
            {
                i = -1;
                return dt;
            }
            object[] v1 = dt.Rows[i - 1].ItemArray, v2 = r.ItemArray;
            dt.Rows[i].ItemArray = v1;
            dt.Rows[i - 1].ItemArray = v2;
            i = i - 1;
            return dt;
        }

        /// <summary>
        /// 将指定的行下移一行
        /// <para>如果移动失败则i=-1</para>
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filter"></param>
        /// <param name="i"></param>
        /// <returns></returns>
        public static DataTable MoveDown(this DataTable dt, string filter, out int i)
        {
            DataRow r = dt.Select(filter).FirstOrDefault();
            if (r == null)
            {
                i = -1;
                return dt;
            }
            i = dt.Rows.IndexOf(r);
            if (i == dt.Rows.Count - 1)
            {
                i = -1;
                return dt;
            }
            object[] v2 = dt.Rows[i + 1].ItemArray, v1 = r.ItemArray;
            dt.Rows[i].ItemArray = v2;
            dt.Rows[i + 1].ItemArray = v1;
            i = i + 1;
            return dt;
        }

        /// <summary>
        /// 将指定的行移到最底部
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        public static DataTable MoveBottom(this DataTable dt, string filter)
        {
            DataRow r = dt.Select(filter).FirstOrDefault();
            if (r == null || dt.Rows.IndexOf(r) == dt.Rows.Count) return dt;
            object[] v = r.ItemArray;
            dt.Rows.Remove(r);
            dt.Rows.Add(v);
            return dt;
        }
        #endregion

        #region 转换操作

        /// <summary>
        /// 数据表转换成字典
        /// <para>如：dt.ToDictionary&lt;int, string&gt;((r, dict) =&gt; { dict.Add((int)r["ID"], r["Name"].ToString()); });</para>
        /// </summary>
        /// <typeparam name="K"></typeparam>
        /// <typeparam name="V"></typeparam>
        /// <param name="dt"></param>
        /// <param name="act"></param>
        /// <returns></returns>
        /// <remarks>2014-2-17 2.0 by sutroon</remarks>
        public static Dictionary<K, V> ToDictionary<K, V>(this DataTable dt, Action<DataRow, Dictionary<K, V>> act)
        {
            Dictionary<K, V> dict = new Dictionary<K, V>();
            foreach (DataRow r in dt.Rows)
            {
                act(r, dict);
            }
            return dict;
        }


        /// <summary>
        /// 转换成带架构信息的字符串
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="colSplitor"></param>
        /// <param name="rowSplitor"></param>
        /// <param name="skipFirstRow"></param>
        /// <param name="showRowHeader"></param>
        /// <returns></returns>
        public static string ToText(this DataTable dt, string colSplitor = " | ", string rowSplitor = "\r\n", bool skipFirstRow = false, bool showRowHeader = false)
        {
            StringBuilder sb = new StringBuilder();
            int i = dt.Columns.Count;
            int n = 0;
            if (!skipFirstRow)
            {
                foreach (DataColumn c in dt.Columns)
                {
                    i--;
                    sb.AppendFormat("{2}{0}{1}", c.ColumnName, i > 0 ? colSplitor : string.Empty, showRowHeader ? "[{0}]".Fmt(n) : string.Empty);
                    n++;
                }
                if (sb.Length > 0) sb.Append(rowSplitor);
            }
            foreach (DataRow r in dt.Rows)
            {
                n = 0;
                i = dt.Columns.Count;
                foreach (object v in r.ItemArray)
                {
                    i--;
                    sb.AppendFormat("{2}{0}{1}", v, i > 0 ? colSplitor : string.Empty, showRowHeader ? "[{0}]".Fmt(n) : string.Empty);
                    n++;
                }
                sb.Append(rowSplitor);
            }
            return sb.ToString();
        }

        /// <summary>
        /// 转换成数据字符串
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="colSplitor"></param>
        /// <param name="rowSplitor"></param>
        /// <returns></returns>
        public static string ToDataText(this DataTable dt, string colSplitor = ",", string rowSplitor = "\n")
        {
            StringBuilder sb = new StringBuilder();
            int i = dt.Columns.Count;
            int n = 0;
            foreach (DataRow r in dt.Rows)
            {
                n = 0;
                i = dt.Columns.Count;
                foreach (object v in r.ItemArray)
                {
                    i--;
                    sb.Append(v);
                    if (i > 0) sb.Append(colSplitor);
                    n++;
                }
                sb.Append(rowSplitor);
            }
            return sb.ToString();
        }

        /// <summary>
        /// 转换成键值字符串
        /// <para>2013-1-26</para>
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="kColId"></param>
        /// <param name="vColId"></param>
        /// <param name="colSplitor"></param>
        /// <param name="rowSplitor"></param>
        /// <returns></returns>
        public static string ToKVText(this DataTable dt, int kColId = 1, int vColId = 0, string colSplitor = "=", string rowSplitor = "&")
        {
            if (dt == null) return string.Empty;
            if (dt.Columns.Count < kColId || dt.Columns.Count < vColId) return string.Empty;
            StringBuilder sb = new StringBuilder();
            foreach (DataRow r in dt.Rows)
            {
                sb.AppendFormat("{0}{1}{2}{3}", r[vColId], colSplitor, r[kColId], rowSplitor);
            }
            return sb.ToString();
        }

        /// <summary>
        /// 转换成Json字符串数据
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        /// <remarks>2014-3-8 by sutroon</remarks>
        public static string ToJsonString(this DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0) return string.Empty;

            StringBuilder sb = new StringBuilder();
            sb.Append("[");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sb.Append("{");
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    sb.Append("\"");
                    sb.Append(dt.Columns[j].ColumnName);
                    sb.Append("\":\"");
                    sb.Append(dt.Rows[i][j].ToString());
                    sb.Append("\",");
                }
                sb.Remove(sb.Length - 1, 1);
                sb.Append("},");
            }
            sb.Remove(sb.Length - 1, 1);
            sb.Append("]");
            return sb.ToString();
        }
        #endregion

        #region 其他操作

        /// <summary>
        /// 将DataRow的值转换为键值字符串
        /// <para>如：ToAttributeString(r) => ID="1" Name="Name1"</para>
        /// </summary>
        /// <param name="r"></param>
        /// <param name="splitor"></param>
        /// <param name="symbol"></param>
        /// <returns></returns>
        /// <remarks>2013-10-29 by sutroon</remarks>
        public static string ToAttributeString(this DataRow r, string splitor = " ", string symbol = "=")
        {
            StringBuilder sb = new StringBuilder();
            foreach (DataColumn c in r.Table.Columns)
            {
                sb.AppendFormat("{0}{1}{2}\"{3}\"", splitor, c.ColumnName, symbol, r[c]);
            }
            return sb.Length > splitor.Length ? sb.Remove(0, splitor.Length).ToString() : sb.ToString();
        }
        #endregion
    }
}
